class Drug < ActiveRecord::Base
  has_and_belongs_to_many :categories
  has_many :ratings

  def self.get_top_drugs
    sql = <<-SQL
      select d.id id, d.name name, count(distinct r.id) cnt
      from drugs d, ratings r 
      where d.id = r.drug_id
      group by d.id
      order by cnt desc
      limit 20
    SQL
    connection.select_all(sql)
  end

  def self.find_by_initial(initial)
    find(:all, :conditions => ["name like ?", initial + "%"], :order => "name ASC")
  end

  def get_rating_count(sex, age, time_taken)
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 when "U"
                   "(r.sex <> 'M' and r.sex <> 'F')"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 when "U"
                   "r.age is null"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        when "U"
                          "r.days_taken is null"
                        else
                          "1"
                        end
    sql = <<-SQL
      select count(distinct r.id) cnt
      from ratings r
      where r.drug_id = #{self[:id]}
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
    SQL
    connection.select_all(sql)[0]["cnt"].to_i
  end

  def get_rating
    Rating.average(:score, :conditions => ["drug_id = ?", self[:id]])
  end

  def get_rating_rank(category_id)
    sql = <<-SQL
      select r.drug_id drug
      from ratings r, categories_drugs cd
      where r.drug_id = cd.drug_id
      and cd.category_id = #{category_id}
      group by r.drug_id
      order by avg(r.score) desc
    SQL
    rating_list = connection.select_all(sql)
    return rating_list.index({"drug" => self[:id].to_s}) + 1, rating_list.length
  end

  def get_popularity(category_id)
    cnt = Rating.count(:conditions => ["drug_id = ?", self[:id]])
    total = Rating.count(:joins => "as r join categories_drugs as cd on r.drug_id = cd.drug_id", :conditions => ["cd.category_id = ?", category_id])
    return cnt, 100.0 * cnt / total
  end

  def get_popularity_rank(category_id)
    sql = <<-SQL
      select r.drug_id drug
      from ratings r, categories_drugs cd
      where r.drug_id = cd.drug_id
      and cd.category_id = #{category_id}
      group by r.drug_id
      order by count(r.id) desc
    SQL
    rating_list = connection.select_all(sql)
    return rating_list.index({"drug" => self[:id].to_s}) + 1, rating_list.length
  end

  def get_sideffects_by_tolerance(sex, age, time_taken)
    get_sideffects(sex, age, time_taken, "T")
  end
  
  def get_sideffects_by_frequency(sex, age, time_taken)
    get_sideffects(sex, age, time_taken, "F")
  end

  def get_sideffects_by_sideffect(sex, age, time_taken)
    get_sideffects(sex, age, time_taken, "S")
  end

  def get_stat(sex, age, time_taken)
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 when "U"
                   "(r.sex <> 'M' and r.sex <> 'F')"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 when "U"
                   "r.age is null"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        when "U"
                          "r.days_taken is null"
                        else
                          "1"
                        end
    sql = <<-SQL
      select count(distinct r.id) cnt
      from ratings r
      where r.drug_id = #{self[:id]}
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
    SQL
    connection.select_all(sql)[0]["cnt"].to_i
  end

  def get_sideffects(sex, age, time_taken, by = "")
    by_filter = case by
                when "T"
                  "order by tolerance desc"
                when "F"
                  "order by cnt desc"
                when "S"
                  "order by name asc"
                else
                  "order by cnt desc"
                end
    sex_filter = case sex
                 when "F"
                   "r.sex = 'F'"
                 when "M"
                   "r.sex = 'M'"
                 else
                   "1"
                 end
    age_filter = case age
                 when "18"
                   "r.age <= 18"
                 when "45"
                   "r.age > 18 and r.age <= 45"
                 when "60"
                   "r.age > 45 and r.age <= 60"
                 when "inf"
                   "r.age > 60"
                 else
                   "1"
                 end
    time_taken_filter = case time_taken 
                        when "week"
                          "r.days_taken <= 7"
                        when "month"
                          "r.days_taken > 7 and r.days_taken <= 30"
                        when "season"
                          "r.days_taken > 30 and r.days_taken <= 90"
                        when "half_year"
                          "r.days_taken > 90 and r.days_taken <= 180"
                        when "year"
                          "r.days_taken > 180 and r.days_taken <= 365"
                        when "inf"
                          "r.days_taken > 365"
                        else
                          "1"
                        end
    sql = <<-SQL
      select s.name name, count(distinct r.id) cnt, avg(r.score) tolerance 
      from sideffects s, ratings r force index(index_ratings_on_drug_id), ratings_sideffects rs
      where r.drug_id = #{self[:id]}
      and r.id = rs.rating_id 
      and s.id = rs.sideffect_id
      and #{sex_filter}
      and #{age_filter}
      and #{time_taken_filter}
      group by rs.sideffect_id 
      #{by_filter}
    SQL
    connection.select_all(sql)
  end

  def get_sideffect_count
    sql = <<-SQL
      select count(distinct rs.sideffect_id) cnt
      from ratings r, ratings_sideffects rs
      where r.drug_id = #{self[:id]}
      and r.id = rs.rating_id
    SQL
    connection.select_all(sql)[0]["cnt"].to_i
  end
end
